/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Persistencia;

import Negocio.Archivo;
import Negocio.Comentario;
import Negocio.Evento;
import Negocio.Usuario;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author JAVA
 */
public class PComentario {
    
    public static void crearComentarioBD(Comentario c, Evento e) {//meteodo que recibe un evento y un comentario y lo graba en la BD
        crearComentarioGeneralBD(c, e.getId(), 1);        
    }
    
    public static void crearComentarioBD(Comentario c, Archivo a) {//meteodo que recibe un archivo y un comentario y lo graba en la BD
        crearComentarioGeneralBD(c, a.getId(), 0);        
    }
    
    public static List<Comentario> devolverListaComentariosGeneralBD(Evento e) {//meteodo que recibe un evento y devuelve lista de comentarios relacionados
        return devolverListaComentariosGeneralBD(e.getId(), 1);        
    }
    
    public static List<Comentario> devolverListaComentariosGeneralBD(Archivo a) {//meteodo que recibe un archivo y devuelve lista de comentarios relacionados
        return devolverListaComentariosGeneralBD(a.getId(), 0);        
    }
    
    public static void eliminarComentarioEventoBD(Comentario c) {//metodo que eliminar un comentario

        Connection connection = null;
        Statement statement = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();
            
            statement.executeUpdate("DELETE FROM comentario WHERE ID = " +c.getId());
        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
            
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }
    
    
    public static Comentario seleccionarComentariolBD(int id) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Comentario c = null;
        
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            resultSet = statement.executeQuery("SELECT id, id_relacion, id_usuario, texto, fecha from comentario WHERE ID = " +id);
            
            if (resultSet.next()) {
                
                Date fechaCreacion = dateFormat.parse(resultSet.getString("fecha"));
                Usuario u = Usuario.selecionarUsuarioLite(resultSet.getInt("id_usuario"));
                
                c = new Comentario (resultSet.getInt("id"), resultSet.getString("texto"), 
                        fechaCreacion, u);
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
        return c;
    }   
    
    
    public static List<Comentario> devolverListaComentariosGeneralBD(int id_relacion, int isEvento) {

        List<Comentario> listComentarios = new ArrayList<Comentario>();
        
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            resultSet = statement.executeQuery("SELECT id from comentario WHERE isEvento = "+isEvento+" AND ID_relacion = " +id_relacion);
            
            while (resultSet.next()) {
                
                Comentario c = seleccionarComentariolBD(resultSet.getInt("id"));                
                listComentarios.add(c);
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
        return listComentarios;
    }   
    
    
    public static void crearComentarioGeneralBD(Comentario c, int id_relacion, int isEvento) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        
        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();
            
            Date fechaCreacion = c.getFechaComentario();
            String stringFechaComentario = dateFormat.format(fechaCreacion);

            statement.executeUpdate("INSERT INTO comentario (id_relacion, id_usuario, texto, fecha, isEvento) values ("
                    + id_relacion + ", " + c.getUsuario().getId() + ", '"
                    + c.getTexto() + "','"+stringFechaComentario+"', "+isEvento+")");
            
            resultSet = statement.executeQuery("SELECT ID FROM comentario ORDER BY ID DESC");
            
            if (resultSet.next()) {
                c.setId(resultSet.getInt("id"));
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }
    
    public static void crearMensajeBD(Comentario c, Usuario ue, Usuario ur, int isPrivado){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        
        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();
            
            Date fechaCreacion = c.getFechaComentario();
            String stringFechaComentario = dateFormat.format(fechaCreacion);

            statement.executeUpdate("INSERT INTO mensaje (id_emisor, id_receptor, contenido,fecha,isPrivado) values ("
                    + ue.getId() + ", " + ur.getId() + ", '"+ c.getTexto() + "','"+stringFechaComentario+"',"+ isPrivado +")");
            
            resultSet = statement.executeQuery("SELECT ID FROM mensaje ORDER BY ID DESC");
            
            if (resultSet.next()) {
                c.setId(resultSet.getInt("id"));
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }
    
     public static List<Comentario> devolverListaMensajesGeneralBD(Usuario u) {

        List<Comentario> listComentarios = new ArrayList<Comentario>();
        
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            resultSet = statement.executeQuery("SELECT id from mensaje WHERE id_receptor = "+u.getId()+" and isPrivado = 1");
            
            while (resultSet.next()) {
                
                Comentario c = seleccionarMensajeBD(resultSet.getInt("id"));                
                listComentarios.add(c);
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
        return listComentarios;
    } 
     
     public static List<Comentario> devolverListaMensajesDePerfilBD(Usuario u) {

        List<Comentario> listComentarios = new ArrayList<Comentario>();
        
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            resultSet = statement.executeQuery("SELECT id from mensaje WHERE id_receptor = "+u.getId()+" and isPrivado = 0");
            
            while (resultSet.next()) {
                
                Comentario c = seleccionarMensajeBD(resultSet.getInt("id"));                
                listComentarios.add(c);
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
        return listComentarios;
    }
     
     public static void eliminarMensajeBD(Comentario c) {//metodo que eliminar un comentario

        Connection connection = null;
        Statement statement = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();
            
            statement.executeUpdate("DELETE FROM mensaje WHERE ID = " +c.getId());
        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
            
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }
     
     public static Comentario seleccionarMensajeBD(int id) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Comentario c = null;
        
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();

            resultSet = statement.executeQuery("SELECT id, id_emisor,id_receptor, contenido, fecha from mensaje WHERE ID = " +id);
            
            if (resultSet.next()) {
                
                Date fechaCreacion = dateFormat.parse(resultSet.getString("fecha"));
                Usuario u = Usuario.selecionarUsuarioLite(resultSet.getInt("id_emisor"));
                
                c = new Comentario (resultSet.getInt("id"), resultSet.getString("contenido"), 
                        fechaCreacion, u);
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
        return c;
    }   
     
     
    
}
